# -*- coding: utf-8 -*-
# Author   : ZhangQing
# Time     : 2025-08-04 23:42
# File     : connection.py
# Project  : codebuddy_craft
# Desc     : 数据库连接
# backend/database/connection.py
"""
数据库连接管理 🗄️

处理数据库连接、会话管理和初始化
"""

import asyncio
import logging
from typing import AsyncGenerator, Optional
from contextlib import asynccontextmanager

from sqlalchemy.ext.asyncio import (
    AsyncSession, AsyncEngine, create_async_engine, async_sessionmaker
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.pool import StaticPool
from sqlalchemy import text, event
from sqlalchemy.engine import Engine

from backend.core.settings import get_settings

# 获取配置
settings = get_settings()
logger = logging.getLogger(__name__)

# 创建基础模型类
Base = declarative_base()

# 全局变量
_async_engine: Optional[AsyncEngine] = None
_async_session_factory: Optional[async_sessionmaker] = None


def get_database_url() -> str:
    """获取数据库连接URL 🔗"""
    db_url = settings.DATABASE_URL

    # 确保SQLite使用异步驱动
    if "sqlite://" in db_url and "aiosqlite" not in db_url:
        db_url = db_url.replace("sqlite://", "sqlite+aiosqlite://")

    logger.info(f"数据库URL: {db_url.split('@')[-1] if '@' in db_url else db_url}")
    return db_url


def create_engine() -> AsyncEngine:
    """创建数据库引擎 ⚙️"""

    db_url = get_database_url()

    # 基础引擎配置
    engine_kwargs = {
        "echo": settings.DATABASE_ECHO,
        "future": True,
    }

    # SQLite特殊配置
    if "sqlite" in db_url:
        engine_kwargs.update({
            "poolclass": StaticPool,
            "connect_args": {
                "check_same_thread": False,
                "timeout": 30,
            }
        })
    else:
        # PostgreSQL/MySQL配置
        engine_kwargs.update({
            "pool_size": settings.DATABASE_POOL_SIZE,
            "max_overflow": settings.DATABASE_MAX_OVERFLOW,
            "pool_pre_ping": True,  # 连接前检查连接是否有效
            "pool_recycle": 1800,   # 30分钟回收连接
            "pool_timeout": 30,     # 连接超时时间
            "pool_use_lifo": True,  # 使用LIFO策略提高缓存命中率
        })

    engine = create_async_engine(db_url, **engine_kwargs)

    # 添加事件监听器
    if "sqlite" in db_url:
        @event.listens_for(engine.sync_engine, "connect")
        def set_sqlite_pragma(dbapi_connection, connection_record):
            """设置SQLite性能优化参数"""
            cursor = dbapi_connection.cursor()
            # 启用外键约束
            cursor.execute("PRAGMA foreign_keys=ON")
            # 设置WAL模式
            cursor.execute("PRAGMA journal_mode=WAL")
            # 设置同步模式
            cursor.execute("PRAGMA synchronous=NORMAL")
            # 设置缓存大小
            cursor.execute("PRAGMA cache_size=10000")
            # 设置临时存储
            cursor.execute("PRAGMA temp_store=memory")
            cursor.close()

    return engine


def get_engine() -> AsyncEngine:
    """获取数据库引擎实例 🔧"""
    global _async_engine

    if _async_engine is None:
        _async_engine = create_engine()
        logger.info("✅ 数据库引擎已创建")

    return _async_engine


def get_session_factory() -> async_sessionmaker:
    """获取会话工厂 🏭"""
    global _async_session_factory

    if _async_session_factory is None:
        engine = get_engine()
        _async_session_factory = async_sessionmaker(
            engine,
            class_=AsyncSession,
            expire_on_commit=False,
            autoflush=False,
            autocommit=False
        )
        logger.info("✅ 会话工厂已创建")

    return _async_session_factory


async def get_db() -> AsyncGenerator[AsyncSession, None]:
    """获取数据库会话依赖项 📋

    FastAPI依赖项，用于在路由中注入数据库会话
    """
    session_factory = get_session_factory()

    async with session_factory() as session:
        try:
            yield session
        except Exception as e:
            logger.error(f"数据库会话错误: {e}")
            await session.rollback()
            raise
        finally:
            await session.close()


@asynccontextmanager
async def get_db_session() -> AsyncGenerator[AsyncSession, None]:
    """获取数据库会话上下文管理器 🔄

    在非FastAPI环境中使用的数据库会话
    """
    session_factory = get_session_factory()

    async with session_factory() as session:
        try:
            yield session
            await session.commit()
        except Exception as e:
            logger.error(f"数据库会话错误: {e}")
            await session.rollback()
            raise
        finally:
            await session.close()


async def init_db() -> None:
    """初始化数据库 🚀

    创建所有表和初始数据
    """
    logger.info("🔧 开始初始化数据库...")

    try:
        # 获取引擎
        engine = get_engine()

        # 导入所有模型以确保它们被注册
        from backend.models.user import User
        from backend.models.project import Project, ProjectCollaborator
        from backend.models.file import File
        # 如果有其他模型，在这里导入

        logger.info("📋 已导入所有数据模型")

        # 创建所有表
        async with engine.begin() as conn:
            # 删除所有表（仅在开发环境）
            if settings.is_development and settings.DEBUG:
                logger.warning("⚠️ 开发模式：删除现有表")
                await conn.run_sync(Base.metadata.drop_all)

            # 创建表
            logger.info("📊 创建数据库表...")
            await conn.run_sync(Base.metadata.create_all)

            logger.info("✅ 数据库表创建完成")

        # 验证数据库连接
        await verify_database_connection()

        # 创建初始数据
        await create_initial_data()
        
        # 启动连接池监控（非SQLite数据库）
        if "sqlite" not in settings.DATABASE_URL:
            from backend.database.pool_monitor import start_pool_monitor
            await start_pool_monitor(engine)

        logger.info("🎉 数据库初始化完成")

    except Exception as e:
        logger.error(f"❌ 数据库初始化失败: {e}")
        raise


async def verify_database_connection() -> bool:
    """验证数据库连接 ✅"""
    try:
        async with get_db_session() as session:
            # 执行简单查询验证连接
            result = await session.execute(text("SELECT 1"))
            result.fetchone()

            logger.info("✅ 数据库连接验证成功")
            return True

    except Exception as e:
        logger.error(f"❌ 数据库连接验证失败: {e}")
        return False


async def create_initial_data() -> None:
    """创建初始数据 📝"""
    logger.info("📝 创建初始数据...")

    try:
        async with get_db_session() as session:
            # 检查是否已有用户
            from backend.models.user import User
            from sqlalchemy import select

            result = await session.execute(select(User).limit(1))
            existing_user = result.scalar_one_or_none()

            if existing_user:
                logger.info("ℹ️ 数据库已有数据，跳过初始数据创建")
                return

            # 创建管理员用户
            if settings.is_development:
                await create_admin_user(session)

            # 创建示例项目模板
            await create_sample_templates(session)

            logger.info("✅ 初始数据创建完成")

    except Exception as e:
        logger.error(f"❌ 创建初始数据失败: {e}")
        # 不抛出异常，允许应用继续运行


async def create_admin_user(session: AsyncSession) -> None:
    """创建管理员用户 👑"""
    from backend.models.user import User
    from backend.utils.security import get_password_hash

    admin_user = User(
        username="admin",
        email="admin@example.com",
        full_name="系统管理员",
        hashed_password=get_password_hash("admin123456"),
        is_active=True,
        is_verified=True,
        preferences={
            "theme": "dark",
            "language": "zh-CN",
            "notifications": {
                "email_notifications": True,
                "push_notifications": True
            }
        }
    )

    session.add(admin_user)
    await session.flush()

    logger.info("👑 管理员用户已创建 (用户名: admin, 密码: admin123456)")


async def create_sample_templates(session: AsyncSession) -> None:
    """创建示例项目模板 📋"""
    # 这里可以创建一些示例项目模板
    # 暂时跳过，后续可以添加
    pass


async def close_db_connections() -> None:
    """关闭数据库连接 🔒"""
    global _async_engine, _async_session_factory

    logger.info("🔒 关闭数据库连接...")

    # 停止连接池监控
    if "sqlite" not in settings.DATABASE_URL:
        try:
            from backend.database.pool_monitor import stop_pool_monitor
            await stop_pool_monitor()
        except Exception as e:
            logger.error(f"停止连接池监控失败: {e}")

    if _async_session_factory:
        # 关闭所有会话
        _async_session_factory = None

    if _async_engine:
        await _async_engine.dispose()
        _async_engine = None

    logger.info("✅ 数据库连接已关闭")


async def reset_database() -> None:
    """重置数据库 ⚠️

    警告：这将删除所有数据！仅在开发环境使用
    """
    if not settings.is_development:
        raise RuntimeError("❌ 只能在开发环境重置数据库")

    logger.warning("⚠️ 重置数据库 - 所有数据将被删除！")

    engine = get_engine()

    async with engine.begin() as conn:
        # 删除所有表
        await conn.run_sync(Base.metadata.drop_all)
        logger.info("🗑️ 已删除所有表")

        # 重新创建表
        await conn.run_sync(Base.metadata.create_all)
        logger.info("📊 已重新创建表")

    # 创建初始数据
    await create_initial_data()

    logger.info("✅ 数据库重置完成")


# 数据库健康检查
async def health_check() -> dict:
    """数据库健康检查 🏥"""
    try:
        async with get_db_session() as session:
            # 执行简单查询
            start_time = asyncio.get_event_loop().time()
            await session.execute(text("SELECT 1"))
            end_time = asyncio.get_event_loop().time()

            response_time = round((end_time - start_time) * 1000, 2)  # 转换为毫秒

            return {
                "status": "healthy",
                "response_time_ms": response_time,
                "database_url": settings.DATABASE_URL.split('@')[-1] if '@' in settings.DATABASE_URL else "sqlite",
                "pool_size": getattr(get_engine().pool, 'size', 'N/A'),
                "checked_out": getattr(get_engine().pool, 'checkedout', 'N/A')
            }

    except Exception as e:
        logger.error(f"数据库健康检查失败: {e}")
        return {
            "status": "unhealthy",
            "error": str(e),
            "database_url": "unknown"
        }


# 数据库备份功能（仅SQLite）
async def backup_database(backup_path: str) -> bool:
    """备份数据库 💾

    仅支持SQLite数据库
    """
    if "sqlite" not in settings.DATABASE_URL:
        logger.error("❌ 数据库备份仅支持SQLite")
        return False

    try:
        import shutil
        from pathlib import Path

        # 获取数据库文件路径
        db_path = settings.DATABASE_URL.replace("sqlite+aiosqlite://", "").replace("sqlite://", "")
        if db_path.startswith("/"):
            db_path = db_path[1:]  # 移除开头的斜杠

        # 确保备份目录存在
        backup_dir = Path(backup_path).parent
        backup_dir.mkdir(parents=True, exist_ok=True)

        # 复制数据库文件
        shutil.copy2(db_path, backup_path)

        logger.info(f"✅ 数据库备份完成: {backup_path}")
        return True

    except Exception as e:
        logger.error(f"❌ 数据库备份失败: {e}")
        return False


# 导出数据库统计信息
async def get_database_stats() -> dict:
    """获取数据库统计信息 📊"""
    try:
        async with get_db_session() as session:
            from backend.models.user import User
            from backend.models.project import Project
            from backend.models.file import File
            from sqlalchemy import func, select

            # 用户统计
            user_count = await session.scalar(select(func.count(User.id)))

            # 项目统计
            project_count = await session.scalar(select(func.count(Project.id)))

            # 文件统计
            file_count = await session.scalar(select(func.count(File.id)))
            total_lines = await session.scalar(select(func.sum(File.lines))) or 0

            return {
                "users": user_count or 0,
                "projects": project_count or 0,
                "files": file_count or 0,
                "total_lines": total_lines,
                "database_size": await get_database_size()
            }

    except Exception as e:
        logger.error(f"获取数据库统计失败: {e}")
        return {
            "users": 0,
            "projects": 0,
            "files": 0,
            "total_lines": 0,
            "database_size": "unknown"
        }


async def get_database_size() -> str:
    """获取数据库大小 📏"""
    try:
        if "sqlite" in settings.DATABASE_URL:
            import os
            from pathlib import Path

            db_path = settings.DATABASE_URL.replace("sqlite+aiosqlite://", "").replace("sqlite://", "")
            if db_path.startswith("/"):
                db_path = db_path[1:]

            if os.path.exists(db_path):
                size_bytes = os.path.getsize(db_path)

                # 转换为可读格式
                for unit in ['B', 'KB', 'MB', 'GB']:
                    if size_bytes < 1024.0:
                        return f"{size_bytes:.1f} {unit}"
                    size_bytes /= 1024.0
                return f"{size_bytes:.1f} TB"

        return "unknown"

    except Exception as e:
        logger.error(f"获取数据库大小失败: {e}")
        return "unknown"
